
from flask import Blueprint, request, render_template
from sqlalchemy import asc,desc,or_,and_,Column,text

from applications.common.utils.http import  table_api, success_api, fail_api
from applications.common.utils.rights import authorize
from applications.models import YeGroup,YeManyRelationship
from applications.schemas import YeGroupSchema
from applications.common.utils.validate import str_escape
from applications.extensions import db
from applications.common.helper import ModelFilter
from applications.common import curd
from applications.common.utils.transactional import transactional
from applications.common.utils.sqlUtil import SqlUtil

'''
分组接口
'''
groupView = Blueprint('groupView', __name__, url_prefix='/ye/group')


# 增加修改
@groupView.post('/saveOrUpdate')
@transactional(rollback = True)
def saveOrUpdate():
    req_getJson = request.get_json(force=True)
    for index in range(len(req_getJson)):
        req_json = req_getJson[index]
        id = req_json.get("id")
        parent_id = req_json.get("parent_id") # 此字段目前没有用到;
        group_id = req_json.get("group_id") # 关联的组id ,如果不传group_id 则 只加一条数据;如果传递了group_id 则 会添加一条组数据并添加关联关系;
        sort = req_json.get("sort")
        name = str_escape(req_json.get("name"))
        type = str_escape(req_json.get("type"))
        remarks = str_escape(req_json.get("remarks"))
        if id is None : 
            # 增加, 或增加关联关系
            d = YeGroup(name=name, sort=sort, type=type, remarks=remarks,parent_id=parent_id)
            db.session.add(d)
            if(group_id is not None) : 
                d2 = YeManyRelationship(mian_id=group_id,second_id=d.id,sort=sort, type='mian-group:second-group')
                db.session.add(d2)
            # db.session.commit()
            # return success_api(msg="增加成功")
        elif id is not None and group_id is not None:
            # 增加关联关系
            d2 = YeManyRelationship(mian_id=group_id,second_id=id,sort=sort, type='mian-group:second-group')
            db.session.add(d2)
        else:
            # 修改
            d = YeGroup.query.filter_by(id=id)
            d.update(dict(name=name, sort=sort, type=type, remarks=remarks,parent_id=parent_id))
            # db.session.commit()
            # b = 1/0
            # return success_api(msg="修改成功")
    return success_api(msg="增加修改成功")



# 查询，通过组类型、关键字、组id 
@groupView.get('/query')
def query():
    # 获取请求参数
    group_type = request.args.get('groupType', type=int) # 组类型
    keyword = request.args.get('keyword', type=str) # 关键字
    if group_type is None: fail_api(msg="查询参数不全")  

    queryList = list()
    queryList.append(YeGroup.type == group_type)
    if keyword is not None and len(keyword) > 0 : queryList.append(YeGroup.name.like('%{name}%'.format(name=keyword)))
    dict_all = YeGroup.query.filter(and_(*queryList)).order_by(asc(YeGroup.sort)).extjs_paginate()

    count = dict_all.total
    data = curd.model_to_dicts(schema=YeGroupSchema, data=dict_all.items)
    return table_api(data=data,count=count)

# 查询，通过关键字、组id 查询关联到组id上的 其他组；
@groupView.get('/queryChildGroup')
def queryChildGroup():
    # 获取请求参数
    keyword = request.args.get('keyword', type=int) # 关键字
    group_id = request.args.get('group_id', type=int) # 关键字
    page = request.args.get('page', type=int) - 1 if request.args.get('page', type=int) is not None else 0# 关键字
    limit = request.args.get('limit', type=int) if request.args.get('limit', type=int) is not None else 20 # 关键字

    if group_id is None: fail_api(msg="查询参数不全")  
    baseSql = '''
        select t.id , group2.name , t.sort,t.second_id,t.mian_id,group2.type
        from ye_many_relationship t
        left join (
            select t.* from ye_group t where t.type = '2'
        ) group2 on group2.id = t.second_id
        where t.mian_id = :mian_id 
        and t.type = 'mian-group:second-group'
        ***<%=keyword%> and group2.name like '%'|| :keyword ||'%' ***
        
        order by t.sort asc
    '''
    # pageSql = '''select t.* from ({content}) t limit :limit offset :page '''.format(content=baseSql)
    # sqlCount = '''select count(t.id) from ({content}) t'''.format(content=baseSql)

    # cursor=db.session.execute(text(pageSql),params={"mian_id":group_id,"keyword":keyword,"limit": limit ,"page": page*limit}) 
    # cursor2=db.session.execute(text(sqlCount),params={"mian_id":group_id,"keyword":keyword,"limit": limit ,"page": page*limit}) 
    # result = cursor.fetchall()
    # count = cursor2.fetchall()[0][0]
    pageData = SqlUtil().dynamicHandleSqlRunPage(baseSql,params={"mian_id":group_id,"keyword":keyword,"limit": limit ,"page": page*limit})
    data = curd.model_to_dicts(schema=YeGroupSchema, data=pageData.get("data"))
    return table_api(data=data,count=pageData.get("total"))

    # relationship_dict_all = YeManyRelationship.query.filter(
    #     YeManyRelationship.mian_id == group_id, 
    #     YeManyRelationship.type == 'mian-group:second-group', 
    # ).order_by(asc(YeManyRelationship.sort)).all()
    # second_group_list = list(map(lambda item : item.second_id,relationship_dict_all))
    # queryList = list()
    # queryList.append(YeGroup.id.in_(second_group_list))
    # if keyword is not None and len(keyword) > 0 : queryList.append(YeGroup.name.like('%{name}%'.format(keyword)))
    # dict_all = YeGroup.query.filter(and_(*queryList)).extjs_paginate()
    # count = dict_all.total
    # data = curd.model_to_dicts(schema=YeGroupSchema, data=dict_all.items)
    # return table_api(data=data,count=count)


# 删除，并删除子类型
@groupView.get('/delete')
@transactional(rollback = True)
def delete():
    # 获取请求参数
    idsStr = request.args.get('ids', type=str)
    type = request.args.get('type', type=str) #  删除标志, all - 删除组以及关联关系; one - 删除组一条数据;
    idList = list(map(lambda item : int(item), idsStr.split(',')))
    if type is not None and type == 'all' : 
        # 默认删除关联关系和自身
        YeManyRelationship.query.filter(
            YeManyRelationship.mian_id.in_(idList), 
            YeManyRelationship.type == 'mian-group:second-group', 
        ).delete()
        YeGroup.query.filter(YeGroup.id.in_(idList)).delete()
    elif type is not None and type == 'one' :
        # 仅删除自身
        YeGroup.query.filter(YeGroup.id.in_(idsStr)).delete()
    elif type is not None and type == 'ship' :
        # 删除关联关系
        YeManyRelationship.query.filter(
            YeManyRelationship.id.in_(idList), 
            YeManyRelationship.type == 'mian-group:second-group', 
        ).delete()
    return success_api(msg="删除成功")














# # 查询，通过类型查全部
# @groupView.get('/queryByType')
# def queryByLevel():
#     # 获取请求参数
#     group_type = request.args.get('groupType', type=int) # 组类型
#     if group_type is None : fail_api(msg="查询参数不全")

#     # orm查询
#     # 使用分页获取data需要.items
#     dict_all = YeGroup.query.filter_by(type=group_type).layui_paginate()
#     count = dict_all.total
#     data = curd.model_to_dicts(schema=YeGroupSchema, data=dict_all.items)
#     return table_api(data=data, count=count)


# # 查询，通过类型查全部
# @groupView.get('/queryByLevel')
# def query():
#     # 获取请求参数
#     group_type = request.args.get('groupType', type=int) # 组类型
#     groupId = request.args.get('groupId', type=int) # 组id
#     groupLevel = request.args.get('groupLevel', type=int) # 查询层级 ，顶级-1,二级-2..... 
    
#     if group_type is None: fail_api(msg="查询参数不全")  
#     if groupLevel is None: groupLevel = -1
#     list_all = list()

#     # 查询顶级节点
#     if groupLevel == 1 : 
#         list_all = YeGroup.query.filter(YeGroup.type == group_type,YeGroup.parent_id == -1).all()
#     else : 
#         list_all = queryChildNode(id = groupId,level = groupLevel)

#     data = curd.model_to_dicts(schema=YeGroupSchema, data=list_all)
#     return table_api(data=data)






# # 删除，并删除子类型
# @groupView.get('/delete')
# @transactional(rollback = True)
# def delete():
#     # 获取请求参数
#     id = request.args.get('id', type=int)
#     idList = queryChildNode(id = id)
#     idList.append(id)
#     YeGroup.query.filter(YeGroup.id.in_(idList)).delete()
#     # db.session.commit()
#     # b = 1/0
#     return success_api(msg="删除成功")







# # 查询某个节点下面的所有节点 
# # id : 父id
# # level : 只获取具体某一层级下的节点；
# # funcLevel : 递归层级 
# def queryChildNode(id : int ,level: int = -1, funcLevel : int = 0):
#     # 递归片段变量
#     idList = list()
#     # if level == -1 : idList.append(id)
#     # 递归层级加一
#     funcLevel = funcLevel + 1 
#     childList = YeGroup.query.filter_by(parent_id=id).all()

#     # 递归出口
#     # if level != -1 and level != funcLevel : return idList
#     if len(childList) <= 0 : return idList

#     # 递归逻辑处理
#     for item in childList :
#         idList.extend(queryChildNode(id = item.id,level = level, funcLevel = funcLevel))
#         if level == -1 or level == funcLevel  : idList.append(item.id)
    
#     return idList



